﻿using System.Drawing;
using System.Globalization;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using KPIS.GERP.Models;
using KPIS.GERP.Models.BGM;
using KPIS.GERP.Models.Class;
using KPIS.GERP.Models.ICM;
using KPIS.GERP.Models.ICM.ViewModel;
using KPIS.GERP.Models.SYS;
using KPIS.GERP.WEB.GIMS.Class;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.Data.Entity;

namespace KPIS.GERP.WEB.GIMS.Controllers
{
    public class InventoryReportController : Controller
    {
        public ActionResult Index()
        {
            ViewBag.Error = TempData["Error"];
            ViewBag.Report = TempData["Report"];
            return View();
        }

        public void Report1(bool DdlShowIsLowerAsset = false)
        {
            string strTemplateFilePath = Server.MapPath("~/Templates/รายงานสำรวจครุภัณฑ์.xlsx");
            string strFileName = string.Format("รายงานสำรวจครุภัณฑ์-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff"));
            using (ExcelPackage pck = new ExcelPackage(new FileInfo(strTemplateFilePath), true))
            {
                ExcelWorksheet ws = pck.Workbook.Worksheets[1];
                // Fill data
                {
                    var service = new AssetService.AssetServiceClient();
                    var serviceMovement = new MovementService.MovementServiceClient();
                    var serviceBudgetSource = new MasBudgetSourceService.MasBudgetSourceServiceClient();

                    var modelFilter = new Asset { RecordStatus = "!D" };
                    var modelFilterIncPath = new[]
                    {
                        "MasUnit", 
                        "MasBudgetSource",
                        "MasResourceItem.MasResourceSubGroup.MasResourceGroup.MasResourceType", 
                        "MasResourceSubGroup.MasResourceGroup.MasResourceType",  
                        "MasResourceGroup.MasResourceType", 
                        "MasResourceType"
                    };

                    List<Asset> models = new List<Asset>();
                    using (var context = new GERPContext(false))
                    {
                        IQueryable<Asset> query = context.Assets;


                        query = modelFilterIncPath.Aggregate(query,
                            (current, includePath) => current.Include(includePath));

                        models = query.Where(o => o.RecordStatus != "D").ToList()
                            .Where(o => o.MasResourceItem != null)
                            .OrderBy(o => o.MasResourceType.Code)
                            .ThenBy(m => m.MasResourceGroup.Code)
                            .ThenBy(m => m.MasResourceSubGroup.Code)
                            .ThenBy(m => (m.AssetCode ?? string.Empty)
                                .Replace(" ", string.Empty))
                            .GroupBy(o => o.AssetId)
                            .Select(o => o.First())
                            .ToList();
                    }


                    /*var models = service.SelectAsset(modelFilter, modelFilterIncPath)
                        /x.Where(o => o.MasResourceItem.MasResourceSubGroup.MasResourceGroup.ResTypeId == 2
                                    && (
                                        (DdlShowIsLowerAsset == false && o.IsLowerAsset != true) ||
                                        (DdlShowIsLowerAsset == true && o.IsLowerAsset == true)
                                        )
                        ).OrderBy(m => m.MasResourceItem.MasResourceSubGroup.MasResourceGroup.ResTypeId)x/
                        .Where(o=>o.MasResourceItem!=null)
                        .OrderBy(o => o.MasResourceType.Code)
                        .ThenBy(m => m.MasResourceGroup.Code)
                        .ThenBy(m => m.MasResourceSubGroup.Code)
                        .ThenBy(m => (m.AssetCode ?? string.Empty)
                            .Replace(" ", string.Empty))

                        .GroupBy(o => o.AssetId)
                        .Select(o=>o.First())
                        .ToList();*/
                    var countAll = models.Count();
                    var countUnDel = models.Where(o => o.RecordStatus != "D").Count();
                    var countDel = models.Where(o => o.RecordStatus == "D").Count();
                    var db = new GERPContext();
                    var dep = db.AssetDepreciations.ToList();
                    foreach (var m in models)
                    {
                        if (dep.Any(o => o.AssetId == m.AssetId))
                        {
                            m.IsLowerAsset = false;
                            continue;
                        }
                    }
                    if (DdlShowIsLowerAsset)
                    {

                        models = models.Where(o => o.IsLowerAsset).ToList();
                    }
                    else
                    {
                        models = models.Where(o => o.IsLowerAsset != true).ToList();
                    }
                    int amountAsset = models.Count;

                    var budgetSources = serviceBudgetSource.Select(null, null);

                    List<MasResourceSubGroup> mSubGroups = new List<MasResourceSubGroup>();
                    List<MasResourceGroup> mGroups = new List<MasResourceGroup>();
                    foreach (var m in models)
                    {
                        if (mSubGroups.All(o => o.ResSubGrpId != m.ResSubGrpId) && m.MasResourceSubGroup != null)
                        {
                            mSubGroups.Add(m.MasResourceSubGroup);
                        }

                        if (mGroups.All(o => o.ResGrpId != m.ResGrpId) && m.MasResourceGroup != null)
                        {
                            mGroups.Add(m.MasResourceGroup);
                        }
                    }

                    foreach (var g in mSubGroups)
                    {
                        if (mGroups.All(o => o.ResGrpId != g.ResGrpId) && g.MasResourceGroup != null)
                        {
                            mGroups.Add(g.MasResourceGroup);
                        }
                    }

                    mGroups = mGroups.OrderBy(o => o.Code).ToList();

                    int nCurrentRow = 4;
                    int cG = 0, cSg = 0, cI = 0;
                    int itemNo = 1;
                    foreach (var mGroup in mGroups)
                    {
                        var nRow1 = nCurrentRow++;
                        string cellGroupExcelId = "A" + nRow1;
                        string cellGroupExcelText = string.Format("{0} - [{2}]{1}",
                            mGroup.Code,
                            mGroup.Name,
                            mGroup.MasResourceType.Name);

                        ws.Cells[cellGroupExcelId].Value = cellGroupExcelText;
                        ws.Cells["A" + nRow1 + ":M" + nRow1].Merge = true;
                        var f = ws.Cells["A" + nRow1].Style.Font;
                        f.Bold = true;
                        f.Size = 18;
                        ws.Cells["A" + nRow1].Style.Font = f;

                        var subGroupX = mSubGroups.Where(o => o.ResGrpId == mGroup.ResGrpId).ToList();
                        int customCount = 0;
                        foreach (var mSubGroup in subGroupX)
                        {
                            var nRow2 = nCurrentRow++;
                            string cellSubGroupExcelId = "A" + nRow2;
                            string cellSubGroupExcelText = string.Format("{0} - {1}", mSubGroup.Code, mSubGroup.Name);
                            ws.Cells[cellSubGroupExcelId].Value = "        " + cellSubGroupExcelText;
                            ws.Cells["A" + nRow2 + ":L" + nRow2].Merge = true;
                            var f2 = ws.Cells["A" + nRow2].Style.Font;
                            f2.Bold = true;
                            f2.Italic = true;
                            f2.Size = 16;
                            ws.Cells["A" + nRow2].Style.Font = f2;

                            List<Asset> itemEach = models.Where(o => o.ResSubGrpId == mSubGroup.ResSubGrpId).ToList();

                            //if (mGroup.Code == "101000")
                            //{
                            //    var xx = "";
                            //}
                            customCount += itemEach.Count;
                            foreach (var model in itemEach)
                            {
                                var masBudgetSource = budgetSources.FirstOrDefault(o => o.BgSourceId == model.BgSourceId);

                                int nCurrentRow2 = nCurrentRow++;
                                ws.Cells["A" + nCurrentRow2].Value = itemNo++;
                                ws.Cells["B" + nCurrentRow2].Value = model.AssetCode;
                                ws.Cells["C" + nCurrentRow2].Value = model.SerialNumber;

                                //ws.Cells["C" + nCurrentRow2].Value = model.MasResourceItem.Name + " - " +
                                //    Regex.Replace((model.AssetName ?? ""), @"^\s+$[\r\n]*", "", RegexOptions.Multiline);
                                string columnAsset = (model.AssetName ?? "") +
                                                     (!string.IsNullOrWhiteSpace(model.Description)
                                                         ? " - " + model.Description.Trim()
                                                         : "");

                                ws.Cells["D" + nCurrentRow2].Value = Regex.Replace(columnAsset, @"^\s+$[\r\n]*", "",
                                    RegexOptions.Multiline);

                                ws.Cells["E" + nCurrentRow2].Value = model.AssetDate.ToShortDateString();

                                ws.Cells["F" + nCurrentRow2].Value = model.Price.ToString("#,##0.00");
                                ws.Cells["G" + nCurrentRow2].Value = masBudgetSource == null ? "" : masBudgetSource.Name;
                                ws.Cells["H" + nCurrentRow2].Value = model.Place;
                                ws.Cells["I" + nCurrentRow2].Value = "";

                                ws.Cells["J" + nCurrentRow2].Value = "";
                                ws.Cells["K" + nCurrentRow2].Value = "";
                                ws.Cells["L" + nCurrentRow2].Value = "";
                                ws.Cells["M" + nCurrentRow2].Value = "";

                                ws.Cells["N" + nCurrentRow2].Value = "";
                            }
                        }
                    }
                }

                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition",
                    string.Format("attachment;  filename={0}.xlsx", HttpUtility.UrlDecode(strFileName, Encoding.UTF8)));
                Response.HeaderEncoding = Encoding.UTF8;
                Response.BinaryWrite(pck.GetAsByteArray());
                pck.Dispose();
            }
        }

        public void Report1Backup()
        {
            var service = new AssetService.AssetServiceClient();
            var serviceMovement = new MovementService.MovementServiceClient();
            MailMerge mailMerge = new MailMerge();
            var models = service.SelectAsset(new Models.ICM.Asset() { RecordStatus = "!D" }
                , new[] { "MasUnit", "MasResourceItem.MasResourceSubGroup.MasResourceGroup.MasResourceType" })
                // .OrderBy(m => m.MasResourceItem.MasResourceSubGroup.MasResourceGroup.ResTypeId)
                .OrderBy(m => m.MasResourceItem.MasResourceSubGroup.ResGrpId)
                // .OrderBy(m => m.MasResourceItem.ResSubGrpId)
                .ThenBy(m => (m.AssetCode ?? string.Empty).Replace(" ", string.Empty)).ToList();

            Dictionary<string, string> Dics = new Dictionary<string, string>();
            int nOldResItemGrpId = 0;
            int nCurrentRow = 4;
            int nItemNo = 1;

            for (int i = 0; i < models.Count; i++)
            {
                var model = models[i];

                if (nOldResItemGrpId != model.ResGrpId)
                {
                    // from requirement, dont split by group
                    Dics.Add("C" + nCurrentRow,
                        model.MasResourceItem.MasResourceSubGroup.MasResourceGroup.Code + " - " +
                        model.MasResourceItem.MasResourceSubGroup.MasResourceGroup.Name);
                    // + "-" + model.MasResourceItem.Name);
                    nCurrentRow++;
                    nOldResItemGrpId = model.ResGrpId;
                    //  strColumeName = mailMerge.GetExcelColumnName(nCurrentRow);
                    nItemNo = 1;
                }

                var modelMoveDtls = serviceMovement.SelectMovementDtl(new MovementDtl()
                {
                    AssetId = model.AssetId,
                    RecordStatus = "!" + RecordStatus.DeletedStatus
                }, new[] { "Movement" });

                for (int j = 0; j < 12; j++)
                {
                    string strColumeName = mailMerge.GetExcelColumnName(j + 1);
                    string strValue = "";
                    switch (j)
                    {
                        case 0:
                            strValue = nItemNo.ToString();
                            break;
                        case 1:
                            strValue = model.AssetCode;
                            break;
                        case 2:
                            // remove last empty line
                            strValue = Regex.Replace((model.AssetName ?? ""), @"^\s+$[\r\n]*", "",
                                RegexOptions.Multiline);
                            break;
                        case 3:
                            strValue = model.AssetDate.ToShortDateString();
                            break;

                        case 4:
                            strValue = model.Price.ToString("#,##0.00");
                            break;
                        case 5:
                            strValue = model.BgSourceId == 1 ? "งบประมาณ" : "สถาบัน";
                            break;
                        case 6:
                            strValue = model.Place;
                            break;
                        case 7:
                            strValue = "";
                            break;
                        case 8:
                            strValue = "";
                            break;
                        case 9:
                            strValue = "";
                            break;
                        case 10:
                            strValue = "";
                            break;
                        case 11:
                            strValue = "";
                            break;
                        case 12:
                            strValue = "";
                            break;
                        default:
                            strValue = "";
                            break;
                    }

                    Dics.Add(strColumeName + nCurrentRow.ToString(), strValue);
                }

                nCurrentRow++;
                nItemNo++;
            }

            mailMerge.MergeExcel(Response, Server.MapPath("~/Templates/รายงานสำรวจครุภัณฑ์.xlsx"),
                string.Format("รายงานสำรวจครุภัณฑ์-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff"))
                , null, Dics);
        }

        public ActionResult Report2(int ResGrpId = 0, int BgSourceId = 0, string RecDate2 = null,
            string CusNoStart2 = "", string CusNoEnd2 = "", string DdlShowUnTransaction = "NotZero")
        {
            DateTime? stockDate = Convert.ToDateTime(RecDate2) == DateTime.MinValue
                ? (DateTime?)null
                : Convert.ToDateTime(RecDate2);
            if (stockDate.HasValue)
            {
                stockDate = stockDate.Value.AddYears(stockDate.Value.Year > 2400 ? -543 : 0);
            }
            ViewBag.Report = "2";

            //if (ResGrpId != 0 && BgSourceId != 0)
            //{
            MailMerge mailMerge = new MailMerge();

            var sosl = new OfficeSuppliesLotService.OfficeSuppliesLotServiceClient();
            var sr = new MasResourceService.MasResourceServiceClient();
            var ss = new MasBudgetSourceService.MasBudgetSourceServiceClient();
            var modelTemp =
                sosl.ReportSuppliesLot(ResGrpId, BgSourceId, DdlShowUnTransaction == "NotZero", stockDate).ToList();
            var model = new List<ReportOfficeSuppliesLot>();
            foreach (var m in modelTemp)
            {
                if (!string.IsNullOrWhiteSpace(CusNoStart2) && !string.IsNullOrWhiteSpace(CusNoEnd2) &&
                    CusNoStart2.Split('-').Length > 1 && CusNoEnd2.Split('-').Length > 1)
                {
                    Int64 nCusNoStart, nCusNoEnd, nCode;
                    if (Int64.TryParse(CusNoStart2.Replace("-", ""), out nCusNoStart) &&
                        Int64.TryParse(CusNoEnd2.Replace("-", ""), out nCusNoEnd) &&
                        Int64.TryParse((m.Code ?? "").Replace("-", ""), out nCode))
                    {
                        if (nCusNoStart > nCode || nCode > nCusNoEnd)
                        {
                            continue;
                        }
                        else
                        {
                            model.Add(m);
                        }
                    }
                    else
                    {
                        model.Add(m);
                    }
                }
                else
                {
                    model.Add(m);
                }
            }

            foreach (var m in model)
            {
                m.Date = null;
                m.RealQty = null;
                m.Space1 = null;
                m.Space2 = null;
                m.Space3 = null;
                m.Space4 = null;
                m.Amount = null;// (m.Price * m.Qty).ToString("#,##0.00");
            }

            model = model.GroupBy(m => new { m.Code, m.Name, m.Unit, m.Price }).Select(m => new ReportOfficeSuppliesLot()
            {
                Code = m.Key.Code,
                Name = m.Key.Name,
                Unit = m.Key.Unit,
                Price = m.Key.Price,
                Qty = m.Sum(g => g.Qty),
                Amount = (m.Key.Price * m.Sum(g => g.Qty)).ToString("#,##0.00"),
            }).ToList();

            DataTable dtb = GimsUtilities.ToDataTable(model, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
            dtb.Columns.Remove(dtb.Columns["Space4"]);
            Dictionary<string, DataTable> dtbs = new Dictionary<string, DataTable>();
            dtbs.Add("A8", dtb);

            Dictionary<string, string> dic = new Dictionary<string, string>();
            //dic.Add("A3", ss.Select(new MasBudgetSource() { BgSourceId = BgSourceId, UsePetition = false, UseRequest = true }, null).FirstOrDefault().Name);
            dic.Add("A3", BgSourceId == 0
                ? "ไม่แยกแหล่งเงิน"
                : (BgSourceId == -1
                    ? "ไม่มีแหล่งเงิน"
                    : (from bgS in ss.Select(null, null) where bgS.BgSourceId == BgSourceId select bgS.Name)
                        .FirstOrDefault()));
            dic.Add("A4",
                "สำรวจ ณ วันที่ " +
                (stockDate.HasValue ? stockDate.Value.ToLongDateString() : "(Error เพราะไม่ระบุวันที่ออกรายงาน"));
            dic.Add("A5", ResGrpId == 0
                ? ""
                : "ประเภท" + (
                    from rg in sr.SelectResourceGroup(null, null)
                    where rg.ResGrpId == ResGrpId
                    select rg.Name).FirstOrDefault());

            mailMerge.MergeExcel(Response, Server.MapPath("~/Templates/RemainInventory.xlsx"),
                string.Format("Inventory-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff"))
                , dtbs, dic);

            Response.End();
            Response.Close();

            TempData["Error"] = "";
            TempData["Report"] = 2;
            return View("Index");
            //}
            //else
            //{
            //    TempData["Error"] = "กรองข้อมูลก่อนออกรายงานไม่ถูกต้อง!!";
            //    TempData["Report"] = 2;
            //    return RedirectToAction("Index");
            //}
        }

        public void Report4(string RecStartDate, string RecEndDate, int BgSourceIdReport4 = 0,
            string CusNoStart = "", string CusNoEnd = "")
        {
            DateTime StartDate = Convert.ToDateTime(RecStartDate);
            DateTime EndDate = Convert.ToDateTime(RecEndDate);
            StartDate = StartDate.AddYears(StartDate.Year > 2400 ? -543 : 0);
            EndDate = EndDate.AddYears(EndDate.Year > 2400 ? -543 : 0);

            var serviceRes = new MasResourceService.MasResourceServiceClient();
            List<ReportOfficeSuppliesLot2> models;/*
                OfficeSupplyLotReport.Report4SuppliesLot(StartDate, EndDate, BgSourceIdReport4)
                    .OrderBy(o => o.ResItemId);*/
            List<ReportOfficeSuppliesLot2> modelsAll;
            var mailMerge = new MailMerge();
            Dictionary<string, object> Dics = new Dictionary<string, object>();
            int nOldResItemId = 0;
            int nCurrentRow = 8;
            int nCountResItem = 0;
            List<VmReport4Summary> sum = new List<VmReport4Summary>();
            List<int> willsetborder = new List<int>();
            decimal prevQty = 0;
            var resourceRelated = OfficeSupplyLotReport.GetResourceItemOffice(StartDate, EndDate, BgSourceIdReport4,
                out models, out modelsAll);
            foreach (var modelRes in resourceRelated)
            {
                var modelOffs = models.Where(o => o.ResItemId == modelRes.ResItemId)
                    .OrderBy(o => o.ResItemCode).ThenBy(o => o.Date).ToList();
                var bringBack = new VmReport4Summary();
                if (nOldResItemId != modelRes.ResItemId)
                {
                    string strResItemName = modelRes.Name;
                    string strResItemCode = modelRes.Code;

                    // กรณีมีการค้นรหัสพัสดุ ค้นเป็นช่วง ใช้ Convert เป็น Int ก่อน แล้ว Compare มากกว่าน้อยกว่า
                    if (!string.IsNullOrWhiteSpace(CusNoStart) && !string.IsNullOrWhiteSpace(CusNoEnd))
                    {
                        Int64 nCusNoStart, nCusNoEnd, nCode;
                        if (Int64.TryParse(CusNoStart.Replace("-", ""), out nCusNoStart) &&
                            Int64.TryParse(CusNoEnd.Replace("-", ""), out nCusNoEnd) &&
                            Int64.TryParse((modelRes.Code ?? "").Replace("-", ""), out nCode))
                        {
                            if (nCusNoStart > nCode || nCode > nCusNoEnd)
                            {
                                continue;
                            }
                        }
                    }
                    else if (!string.IsNullOrWhiteSpace(CusNoStart) || !string.IsNullOrWhiteSpace(CusNoEnd))
                    {
                        // กรณีมีการค้นรหัสพัสดุ ค้นไม่เป็นช่วง อาจกรอกในช่องซ้ายหรือขวาก็ได้
                        string oneKeyword = (CusNoStart + CusNoEnd).Replace(" ", "").Replace("-", "");
                        string nCode = (modelRes.Code ?? "").Replace(" ", "").Replace("-", "");
                        if (!nCode.ToLower().Contains(oneKeyword.ToLower())) continue;
                    }

                    if (nCountResItem > 0)
                    {
                        Dics.Add("B" + nCurrentRow, "รวม");
                        Dics.Add("C" + nCurrentRow, sum.Sum(o => o.Qty));
                        // Dics.Add("D" + nCurrentRow, sum.Sum(o => o.Price));
                        Dics.Add("E" + nCurrentRow, sum.Sum(o => o.Cost));
                        Dics.Add("F" + nCurrentRow, sum.Sum(o => o.QtyE));
                        // Dics.Add("G" + nCurrentRow, sum.Sum(o => o.PriceE));
                        Dics.Add("H" + nCurrentRow, sum.Sum(o => o.CostE));
                        var lastItem = sum.LastOrDefault();
                        var lastQtyR = lastItem != null ? lastItem.QtyR : 0;
                        var lastCostR = lastItem != null ? lastItem.CostR : 0;
                        Dics.Add("I" + nCurrentRow, lastQtyR);
                        Dics.Add("J" + nCurrentRow, lastCostR);
                        sum.Clear();
                        prevQty = 0;
                        nCurrentRow++;
                    }
                    nCountResItem++;
                    string strResGrpName = modelRes.MasResourceSubGroup.MasResourceGroup.Name;
                    string strUnitName = modelRes.MasUnit.Name;
                    Dics.Add("A" + nCurrentRow,
                        string.Format("ชื่อสินค้า/วัตถุดิบ : {0} ({1})", strResItemName, strResItemCode));
                    nCurrentRow++;
                    Dics.Add("A" + nCurrentRow,
                        string.Format("ชนิด/ขนาด : {0}       ปริมาณนับเป็น : {1}", strResGrpName, strUnitName));
                    nOldResItemId = modelRes.ResItemId;
                    nCurrentRow++;

                    var modelPrev = OfficeSupplyLotReport.Report4SuppliesLotPrev(StartDate, modelRes.ResItemId,
                        BgSourceIdReport4);
                    Dics.Add("A" + nCurrentRow, StartDate.ToShortDateString());
                    Dics.Add("B" + nCurrentRow, "ยกมา");
                    Dics.Add("C" + nCurrentRow, modelPrev.Qty);
                    Dics.Add("D" + nCurrentRow, modelPrev.Price);
                    Dics.Add("E" + nCurrentRow, modelPrev.Amount);
                    Dics.Add("I" + nCurrentRow, modelPrev.Qty);
                    Dics.Add("J" + nCurrentRow, modelPrev.Amount);
                    prevQty = modelPrev.Qty;
                    willsetborder.Add(nCurrentRow);
                    bringBack = new VmReport4Summary
                    {
                        Qty = modelPrev.Type == "T" ? modelPrev.Qty : 0,
                        Cost = modelPrev.Type == "T" ? modelPrev.Amount : 0,

                        QtyE = modelPrev.Type == "R" ? modelPrev.Qty : 0,
                        CostE = modelPrev.Type == "R" ? modelPrev.Amount : 0,

                        QtyR = modelPrev.Qty,
                        // Convert.ToDecimal(Dics.ContainsKey("I"+(nCurrentRow - 1).ToString())?Dics["I" + (nCurrentRow - 1)]:0) + ((modelPrev.Type == "R" ? -1 : 1) * modelPrev.Amount),
                        CostR =
                            Convert.ToDecimal(Dics.ContainsKey("J" + (nCurrentRow - 1).ToString())
                                ? Dics["J" + (nCurrentRow - 1)]
                                : 0) + ((modelPrev.Type == "R" ? -1 : 1) * modelPrev.Amount),
                    };
                    sum.Add(bringBack);
                    nCurrentRow++;
                }

                foreach (var model in modelOffs)
                {

                    willsetborder.Add(nCurrentRow);
                    var transac = AddRowItem(model, ref mailMerge, ref nCurrentRow, ref Dics, ref prevQty);
                    sum.Add(transac);
                }
            }

            if (nCountResItem > 0)
            {
                Dics.Add("B" + nCurrentRow, "รวม");
                Dics.Add("C" + nCurrentRow, sum.Sum(o => o.Qty));
                // Dics.Add("D" + nCurrentRow, sum.Sum(o => o.Price));
                Dics.Add("E" + nCurrentRow, sum.Sum(o => o.Cost));
                Dics.Add("F" + nCurrentRow, sum.Sum(o => o.QtyE));
                // Dics.Add("G" + nCurrentRow, sum.Sum(o => o.PriceE));
                Dics.Add("H" + nCurrentRow, sum.Sum(o => o.CostE));
                var lastItem = sum.LastOrDefault();
                var lastQtyR = lastItem != null ? lastItem.QtyR : 0;
                var lastCostR = lastItem != null ? lastItem.CostR : 0;
                Dics.Add("I" + nCurrentRow, lastQtyR);
                Dics.Add("J" + nCurrentRow, lastCostR);
                sum.Clear();
                willsetborder.Add(nCurrentRow);
            }
            string place = "สถาบันรับรองคุณภาพสถานพยาบาล (องค์การมหาชน) ";
            string bgSourceText = "";
            switch (BgSourceIdReport4)
            {
                case 0:
                    bgSourceText += " ไม่แยกแหล่งเงิน";
                    break;
                case -1:
                    bgSourceText += " เฉพาะข้อมูลที่ไม่มีแหล่งเงิน";
                    break;
                case 1:
                    bgSourceText += " เงินงบประมาณ";
                    break;
                default:
                    bgSourceText += " เงินรายได้";
                    break;
            }
            Dics.Add("C2", place);
            Dics.Add("C4", place + " " + bgSourceText);
            Dics.Add("C3", RecStartDate + " ถึง " + RecEndDate);

            //mailMerge.MergeExcel(Response, Server.MapPath("~/Templates/รายงานสินค้าและวัตถุดิบ.xlsx"),
            //    string.Format("รายงานสินค้าและวัตถุดิบ-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff"))
            //   , null, Dics);
            string path = HttpContext.Server.MapPath("~/Templates/รายงานสินค้าและวัตถุดิบ.xlsx");
            FileInfo file = new FileInfo(path);

            using (ExcelPackage pck = new ExcelPackage(file, true))
            {
                if (pck.Workbook.Worksheets.Count > 0)
                {
                    ExcelWorksheet ws = pck.Workbook.Worksheets[1];

                    foreach (var Dic in Dics)
                    {
                        ws.Cells[Dic.Key].Value = Dic.Value;
                        ws.Cells[Dic.Key].Style.Font.Name = "TH SarabunPSK";
                    }
                    foreach (var i in willsetborder)
                    {
                        ws.Cells[i, 2].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                        ws.Cells[i, 3].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                        ws.Cells[i, 4].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                        ws.Cells[i, 5].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                        ws.Cells[i, 6].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                        ws.Cells[i, 7].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                        ws.Cells[i, 8].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                        ws.Cells[i, 9].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                        ws.Cells[i, 2].Style.Border.Right.Color.SetColor(Color.Black);
                        ws.Cells[i, 3].Style.Border.Right.Color.SetColor(Color.Black);
                        ws.Cells[i, 4].Style.Border.Right.Color.SetColor(Color.Black);
                        ws.Cells[i, 5].Style.Border.Right.Color.SetColor(Color.Black);
                        ws.Cells[i, 6].Style.Border.Right.Color.SetColor(Color.Black);
                        ws.Cells[i, 7].Style.Border.Right.Color.SetColor(Color.Black);
                        ws.Cells[i, 8].Style.Border.Right.Color.SetColor(Color.Black);
                        ws.Cells[i, 9].Style.Border.Right.Color.SetColor(Color.Black);
                    }
                    for (int i = 1; i <= ws.Dimension.End.Row; i++)
                    {
                        if (ws.Cells["B" + i].Value == "รวม")
                        {
                            ws.Cells[i, 2].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                            ws.Cells[i, 3].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                            ws.Cells[i, 4].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                            ws.Cells[i, 5].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                            ws.Cells[i, 6].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                            ws.Cells[i, 7].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                            ws.Cells[i, 8].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                            ws.Cells[i, 9].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                            ws.Cells[i, 2].Style.Border.Right.Color.SetColor(Color.Black);
                            ws.Cells[i, 3].Style.Border.Right.Color.SetColor(Color.Black);
                            ws.Cells[i, 4].Style.Border.Right.Color.SetColor(Color.Black);
                            ws.Cells[i, 5].Style.Border.Right.Color.SetColor(Color.Black);
                            ws.Cells[i, 6].Style.Border.Right.Color.SetColor(Color.Black);
                            ws.Cells[i, 7].Style.Border.Right.Color.SetColor(Color.Black);
                            ws.Cells[i, 8].Style.Border.Right.Color.SetColor(Color.Black);
                            ws.Cells[i, 9].Style.Border.Right.Color.SetColor(Color.Black);

                            ws.Cells[i, 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                            ws.Cells[i, 2].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                            ws.Cells[i, 3].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                            ws.Cells[i, 4].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                            ws.Cells[i, 5].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                            ws.Cells[i, 6].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                            ws.Cells[i, 7].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                            ws.Cells[i, 8].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                            ws.Cells[i, 9].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                            ws.Cells[i, 10].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                            ws.Cells[i, 1].Style.Border.Bottom.Color.SetColor(Color.Black);
                            ws.Cells[i, 2].Style.Border.Bottom.Color.SetColor(Color.Black);
                            ws.Cells[i, 3].Style.Border.Bottom.Color.SetColor(Color.Black);
                            ws.Cells[i, 4].Style.Border.Bottom.Color.SetColor(Color.Black);
                            ws.Cells[i, 5].Style.Border.Bottom.Color.SetColor(Color.Black);
                            ws.Cells[i, 6].Style.Border.Bottom.Color.SetColor(Color.Black);
                            ws.Cells[i, 7].Style.Border.Bottom.Color.SetColor(Color.Black);
                            ws.Cells[i, 8].Style.Border.Bottom.Color.SetColor(Color.Black);
                            ws.Cells[i, 9].Style.Border.Bottom.Color.SetColor(Color.Black);
                            ws.Cells[i, 10].Style.Border.Bottom.Color.SetColor(Color.Black);

                        }
                    }

                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    string fileName = HttpUtility.UrlDecode(
                        string.Format("สินค้าและวัตถุดิบ-{0}",
                            DateTime.Now.ToString("yyyyMMddHHmmssffff")),
                        Encoding.UTF8);
                    Response.AddHeader("content-disposition", string.Format("attachment;  filename={0}.xlsx", fileName));
                    Response.HeaderEncoding = Encoding.UTF8;
                    Response.BinaryWrite(pck.GetAsByteArray());
                    pck.Dispose();
                }
            }
        }

        private VmReport4Summary AddRowItem(ReportOfficeSuppliesLot2 model, ref MailMerge mailMerge,
            ref int nCurrentRow, ref Dictionary<string, object> Dics,
            ref decimal prevQty)
        {
            VmReport4Summary sum = new VmReport4Summary();
            sum.ResItemId = model.ResItemId;
            if (model.Code == "RC58050002")
            {
                var x = "";
            }
            for (int j = 0; j < 10; j++)
            {
                string strColumeName = mailMerge.GetExcelColumnName(j + 1);
                object strValue = null;

                switch (j)
                {
                    case 0:
                        strValue = model.Date.ToShortDateString();
                        break;
                    case 1:
                        strValue = model.Code;
                        break;
                    case 2:
                        if (model.Type == "R")
                        {
                            strValue = model.Qty;
                            sum.Qty = model.Qty;
                        }
                        break;
                    case 3:
                        if (model.Type == "R")
                        {
                            strValue = model.Price;
                            sum.Price = model.Price;
                        }

                        break;
                    case 4:
                        if (model.Type == "R")
                        {
                            var cost = (model.Price * model.Qty);
                            strValue = cost;
                            sum.Cost = cost;
                        }
                        break;
                    case 5:
                        if (model.Type == "D")
                        {
                            strValue = model.Qty;
                            sum.QtyE = model.Qty;
                        }
                        break;
                    case 6:
                        if (model.Type == "D")
                        {
                            strValue = model.Price;
                            sum.PriceE = model.Price;
                        }
                        break;
                    case 7:
                        if (model.Type == "D")
                        {
                            var costE = (model.Price * model.Qty);
                            strValue = costE;
                            sum.CostE = costE;
                        }
                        break;
                    case 8:
                        //strValue = "";
                        // decimal dPrevQty = Convert.ToDecimal(Dics[(model.Type == "R"? "C":"F") + (nCurrentRow - (model.Type == "R"? 1:0))]);

                        if (model.Type == "R")
                        {
                            var qtyR = (prevQty + model.Qty);
                            strValue = qtyR;
                            sum.QtyR = qtyR;
                            prevQty += model.Qty;
                        }
                        else
                        {
                            var qtyR = (prevQty - model.Qty);
                            strValue = qtyR;
                            sum.QtyR = qtyR;
                            prevQty -= model.Qty;
                        }
                        break;
                    case 9:
                        decimal dPrevAmount = Convert.ToDecimal(Dics["J" + (nCurrentRow - 1)]);

                        if (model.Type == "R")
                        {
                            var costR = (dPrevAmount + model.Amount);
                            strValue = costR;
                            sum.CostR = costR;
                        }
                        else
                        {
                            var costR = (dPrevAmount - model.Amount);
                            strValue = costR;
                            sum.CostR = costR;
                        }
                        break;
                    default:
                        strValue = "";
                        break;
                }

                Dics.Add(strColumeName + nCurrentRow, strValue);
            }
            nCurrentRow++;
            return sum;
        }

        public void Report5(string DepricateDateStart, string DepricateDateEnd)
        {
            DateTime dtDpcStart = Convert.ToDateTime(DepricateDateStart);
            DateTime dtDpcEnd = Convert.ToDateTime(DepricateDateEnd);
            dtDpcStart = dtDpcStart.AddYears(dtDpcStart.Year > 2400 ? -543 : 0);
            dtDpcEnd = dtDpcEnd.AddYears(dtDpcEnd.Year > 2400 ? -543 : 0);
            string fileName = HttpUtility.UrlDecode(string.Format("รายงานค่าเสื่อม{0}-{1}"
                , dtDpcStart.ToString("yy-MM", new CultureInfo("th-TH"))
                , dtDpcEnd.ToString("yy-MM", new CultureInfo("th-TH"))), Encoding.UTF8);

            var db = new GERPContext(false);

            var mvh = db.MovementHistorys.Where(o => o.RecordStatus != "D" &&
                o.CreatedWhen >= dtDpcStart.Date && o.CreatedWhen <= dtDpcEnd.Date)
                .Select(o => o.AssetId).ToList();
            //var mvh = db.MovementHistorys.Where(o => o.RecordStatus != "D").ToList();
            //var dpos = db.Disposes.Where(o => o.RecordStatus != "D" && mvh.Contains(mvh., o.DisposeId)).ToList()

            //var mvh = (from move in db.MovementHistorys
            //          join dis in db.Disposes on move.MoveRefNo equals dis.DisposeId
            //          where move.RecordStatus != "D"
            //          && dis.RecordStatus != "D"
            //          select new { AssetId = move.AssetId, Date = dis.DisposeDate }).ToList();

            var parcelsAsset = db.Assets.Include(o => o.ReceiveDtl.Received).Include(o => o.MasDepreciationType)
                .Include(o => o.MasResourceItem.MasResourceSubGroup.MasResourceGroup.MasDepreciationType)
                .Where(o => o.RecordStatus != "D" || mvh.Contains(o.AssetId))
                //.Where(o => mvh.Contains(o.AssetId))
                .Where(o => o.AssetDate <= dtDpcEnd.Date)
                .OrderBy(o => o.BgSourceId)
                .ThenBy(o => o.MasResourceItem.MasResourceSubGroup.MasResourceGroup.Code)
                .ThenBy(o => o.AssetDate)
                .ThenBy(o => o.AssetCode).ToList();

            var resItemIds = parcelsAsset.GroupBy(o => o.ResItemId).Select(o => o.Key).ToArray();
            var dpcs = db.AssetDepreciations.Include(o => o.Asset.ReceiveDtl.Received)
                    .Where(o => o.RecordStatus != "D").ToList();

            //var dpos = db.Disposes.Where(o => o.RecordStatus != "D").ToList();

            var fileExcel = new FileInfo(HttpContext.Server.MapPath("~/Templates/ค่าเสื่อม.xlsx"));
            string strOldResGrpId = "";
            string strOldBgSourceId = "";
            double dPrice = 0;
            double dPrevDep = 0;
            double dDep = 0;
            double dRemain = 0;
            double dDisAmount = 0;

            double dPriceS = 0;
            double dPrevDepS = 0;
            double dDepS = 0;
            double dRemainS = 0;
            double dDisAmountS = 0;

            double dPriceA = 0;
            double dPrevDepA = 0;
            double dDepA = 0;
            double dRemainA = 0;
            double dDisAmountA = 0;

            using (var pck = new ExcelPackage(fileExcel, true))
            {
                ExcelWorksheet ws = pck.Workbook.Worksheets[1];
                ws.Cells[1, 8].Value = @"ค่าเสื่อมราคา
สะสม 
ณ วันที่ 
" + DepricateDateStart + @"
(3)";
                ws.Cells[1, 9].Value = @"ค่าเสื่อมตั้งแต่
" + dtDpcStart.ToString("MM-yy", new CultureInfo("th-TH")) + @" - " +
  dtDpcEnd.ToString("MM-yy", new CultureInfo("th-TH")) + @"
(1)x(2)xจำนวนเดือน
/12
(4)";
                ws.Cells[1, 10].Value = @"มูลค่าคงเหลือ
ณ วันที่ " + dtDpcEnd.ToShortDateString() + @"
(1)-(3)-(4) ";
                ws.Cells[1, 8].Style.WrapText = true;
                ws.Cells[1, 9].Style.WrapText = true;
                ws.Cells[1, 10].Style.WrapText = true;

                int line = 3;
                foreach (var asset in parcelsAsset)
                {
                    decimal? deperateyearPrice = null;

                    if (dpcs.All(o => o.AssetId != asset.AssetId) || asset.IsLowerAsset == true)
                    {
                        continue;
                    }

                    var depPreviousStartDate = dpcs.Where(o => o.AssetId == asset.AssetId &&
                                              o.CalculatedDate.Date <
                                              dtDpcStart.Date);

                    var depBetweenDates = dpcs.Where(o => o.AssetId == asset.AssetId &&
                                               o.CalculatedDate.Date >= dtDpcStart.Date &&
                                               o.CalculatedDate.Date <= dtDpcEnd.Date);

                    decimal priceDepPreviousStartDate = depPreviousStartDate.Sum(o => o.DepAmount);
                    decimal priceDepBetweenDates = depBetweenDates.Sum(o => o.DepAmount);

                    //if (a.MasDepreciationType != null && a.MasDepreciationType.DpRateYear > 0)
                    //{
                    //    deperateyearPrice = a.MasDepreciationType.DpRateYear;
                    //} 

                    if (asset.LifeYear.HasValue && asset.LifeYear > 0)
                    {
                        deperateyearPrice = 100 / asset.LifeYear;
                    }

                    if (asset.MasResourceItem.MasResourceSubGroup.MasResourceGroup.ResGrpId.ToString() != strOldResGrpId && strOldResGrpId != "")
                    {
                        line++;
                        ws.Cells[line, 3].Value = "รวม";
                        ws.Cells[line, 6].Value = dPrice;
                        ws.Cells[line, 8].Value = dPrevDep;
                        ws.Cells[line, 9].Value = dDep;
                        ws.Cells[line, 10].Value = dRemain;
                        ws.Cells[line, 12].Value = dDisAmount;
                        line++;
                    }

                    if (asset.BgSourceId.ToString() != strOldBgSourceId && strOldBgSourceId != "")
                    {
                        line++;
                        ws.Cells[line, 3].Value = "รวม " + (strOldBgSourceId == "1" ? "เงินงบประมาณ" : "เงินรายได้");
                        ws.Cells[line, 6].Value = dPriceS;
                        ws.Cells[line, 8].Value = dPrevDepS;
                        ws.Cells[line, 9].Value = dDepS;
                        ws.Cells[line, 10].Value = dRemainS;
                        ws.Cells[line, 12].Value = dDisAmountS;
                        line++;
                    }

                    if (asset.BgSourceId.ToString() != strOldBgSourceId)
                    {
                        ws.Cells[line, 1].Style.Font.Bold = true;
                        ws.Cells[line, 1].Style.Font.Size = 20;
                        ws.Cells[line, 1].Value = asset.BgSourceId == 1 ? "เงินงบประมาณ" : "เงินรายได้";
                        strOldBgSourceId = asset.BgSourceId.ToString();
                        line++;

                        dPriceS = 0;
                        dPrevDepS = 0;
                        dDepS = 0;
                        dRemainS = 0;
                        dDisAmountS = 0;
                        line++;
                    }

                    if (asset.MasResourceItem.MasResourceSubGroup.MasResourceGroup.ResGrpId.ToString() != strOldResGrpId)
                    {
                        ws.Cells[line, 1].Style.Font.Bold = true;
                        ws.Cells[line, 1].Style.Font.Size = 20;
                        ws.Cells[line, 1].Value = asset.MasResourceItem.MasResourceSubGroup.MasResourceGroup.Name;
                        strOldResGrpId = asset.MasResourceItem.MasResourceSubGroup.MasResourceGroup.ResGrpId.ToString();
                        
                        dPrice = 0;
                        dPrevDep = 0;
                        dDep = 0;
                        dRemain = 0;
                        dDisAmount = 0;
                        line++;
                    }

                    ws.Cells[line, 1].Value = asset.AssetCode;
                    ws.Cells[line, 2].Value = asset.SerialNumber;
                    ws.Cells[line, 3].Value = asset.AssetName + " (" + asset.RecordStatus + ")";
                    ws.Cells[line, 4].Value = asset.AssetDate.ToShortDateString();
                    ws.Cells[line, 5].Value = asset.ReceiveDtl.Received.ReceiveCode;
                    ws.Cells[line, 6].Value = asset.Price;
                    ws.Cells[line, 7].Value = deperateyearPrice ?? 0;
                    ws.Cells[line, 8].Value = priceDepPreviousStartDate;
                    ws.Cells[line, 9].Value = priceDepBetweenDates;
                    ws.Cells[line, 10].Value = (decimal)asset.Price - priceDepPreviousStartDate - priceDepBetweenDates;

                    dPrice += asset.Price;
                    dPrevDep += Convert.ToDouble(priceDepPreviousStartDate);
                    dDep += Convert.ToDouble(priceDepBetweenDates);
                    dRemain += Convert.ToDouble((decimal)asset.Price - priceDepPreviousStartDate - priceDepBetweenDates);

                    dPriceS += asset.Price;
                    dPrevDepS += Convert.ToDouble(priceDepPreviousStartDate);
                    dDepS += Convert.ToDouble(priceDepBetweenDates);
                    dRemainS += Convert.ToDouble((decimal)asset.Price - priceDepPreviousStartDate - priceDepBetweenDates);

                    dPriceA += asset.Price;
                    dPrevDepA += Convert.ToDouble(priceDepPreviousStartDate);
                    dDepA += Convert.ToDouble(priceDepBetweenDates);
                    dRemainA += Convert.ToDouble((decimal)asset.Price - priceDepPreviousStartDate - priceDepBetweenDates);

                    if (asset.RecordStatus == "D")
                    {
                        var mmInfo = db.MovementHistorys.Where(o => o.AssetId == asset.AssetId && o.MovementType == "Dispose").FirstOrDefault();
                        var disposeInfo = db.Disposes.Where(o => o.DisposeId == mmInfo.MoveRefNo).FirstOrDefault();
                        DateTime? dtDispose = disposeInfo.DisposeDate;
                        if (dtDispose != null && dtDispose.Value <= dtDpcEnd)
                        {
                            ws.Cells[line, 11].Value = dtDispose == null ? "" : dtDispose.Value.ToShortDateStringBuddhist();
                            ws.Cells[line, 12].Value = (decimal)asset.Price - priceDepPreviousStartDate - priceDepBetweenDates;
                            dDisAmount += Convert.ToDouble((decimal)asset.Price - priceDepPreviousStartDate - priceDepBetweenDates);
                            dDisAmountS += Convert.ToDouble((decimal)asset.Price - priceDepPreviousStartDate - priceDepBetweenDates);
                            dDisAmountA += Convert.ToDouble((decimal)asset.Price - priceDepPreviousStartDate - priceDepBetweenDates);

                            ws.Cells[line, 10].Value = 0;
                            dRemain -= Convert.ToDouble((decimal)asset.Price - priceDepPreviousStartDate - priceDepBetweenDates);
                            dRemainS -= Convert.ToDouble((decimal)asset.Price - priceDepPreviousStartDate - priceDepBetweenDates);
                            dRemainA -= Convert.ToDouble((decimal)asset.Price - priceDepPreviousStartDate - priceDepBetweenDates);
                        }
                        else
                        {
                            ws.Cells[line, 11].Value = null;
                            ws.Cells[line, 12].Value = null;
                        }
                    }
                    else
                    {
                        ws.Cells[line, 11].Value = null;
                        ws.Cells[line, 12].Value = null;
                    }

                    ws.Cells[line, 11].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                    ws.Cells[line, 11].Style.Border.Right.Color.SetColor(Color.Black);
                    ws.Cells[line, 11].Style.Border.Left.Style = ExcelBorderStyle.Thin;
                    ws.Cells[line, 11].Style.Border.Left.Color.SetColor(Color.Black);
                    ws.Cells[line, 11].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                    ws.Cells[line, 11].Style.Border.Top.Color.SetColor(Color.Black);
                    ws.Cells[line, 11].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                    ws.Cells[line, 11].Style.Border.Bottom.Color.SetColor(Color.Black);

                    line++;
                }

                line++;
                ws.Cells[line, 3].Value = "รวม";
                ws.Cells[line, 6].Value = dPrice;
                ws.Cells[line, 8].Value = dPrevDep;
                ws.Cells[line, 9].Value = dDep;
                ws.Cells[line, 10].Value = dRemain;
                ws.Cells[line, 12].Value = dDisAmount;
                line++;

                line++;
                ws.Cells[line, 3].Value = "รวม " + (strOldBgSourceId == "1" ? "เงินงบประมาณ" : "เงินรายได้");
                ws.Cells[line, 6].Value = dPriceS;
                ws.Cells[line, 8].Value = dPrevDepS;
                ws.Cells[line, 9].Value = dDepS;
                ws.Cells[line, 10].Value = dRemainS;
                ws.Cells[line, 12].Value = dDisAmountS;
                line++;

                line++;
                ws.Cells[line, 3].Value = "รวม ทั้งหมด";
                ws.Cells[line, 6].Value = dPriceA;
                ws.Cells[line, 8].Value = dPrevDepA;
                ws.Cells[line, 9].Value = dDepA;
                ws.Cells[line, 10].Value = dRemainA;
                ws.Cells[line, 12].Value = dDisAmountA;
                line++;

                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", string.Format("attachment;  filename={0}.xlsx", fileName));
                Response.HeaderEncoding = Encoding.UTF8;
                Response.BinaryWrite(pck.GetAsByteArray());
                pck.Dispose();
            }
        }
    }
}